﻿/// -------页面描述：导航管理数据访问类
/// -------创建日期：2010年04月12日 
/// -------创建人：wang
using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using webnew;
using webnew.Model;
using webnew.DBUtility;

namespace webnew.DAL
{

    public class Sys_NavigationHelper
    {

        private SqlHelper DbHelper;

        public Sys_NavigationHelper()
        {
            DbHelper = new SqlHelper();
        }

        /// <summary>
        /// 新增一个导航节点实体
        /// </summary>
        public int AddNav(Model.Sys_NavigationInfo model)
        {
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("INSERT INTO Sys_Navigation(");
            strSql.Append("Id,NaviName,pagesrc,picsrc,ParentID,SysType,Sort,ClassName,MoudelTitle,IsPage,remark,isdel)");
            strSql.Append("VALUES(");
            strSql.Append("@In_Id,@In_NaviName,@In_pagesrc,@In_picsrc,@In_ParentID,@In_SysType,@In_Sort,@In_ClassName,@In_MoudelTitle,@In_IsPage,@In_remark,@In_isdel)");
            System.Data.SqlClient.SqlParameter[] CmdParams = new System.Data.SqlClient.SqlParameter[] {
                    DbHelper.CreateInSqlParameter("@In_Id",DbType.AnsiString,Guid.NewGuid().ToString()),
                    DbHelper.CreateInSqlParameter("@In_NaviName",DbType.AnsiString,model.NaviName),
                    DbHelper.CreateInSqlParameter("@In_pagesrc",DbType.AnsiString,model.pagesrc),
                    DbHelper.CreateInSqlParameter("@In_picsrc",DbType.AnsiString,model.picsrc),
                    DbHelper.CreateInSqlParameter("@In_ParentID",DbType.String,model.ParentID),
                    DbHelper.CreateInSqlParameter("@In_SysType",DbType.String,model.SysType),
                    DbHelper.CreateInSqlParameter("@In_Sort",DbType.Int32,model.Sort),
                    DbHelper.CreateInSqlParameter("@In_ClassName",DbType.AnsiString,model.ClassName),
                    DbHelper.CreateInSqlParameter("@In_MoudelTitle",DbType.AnsiString,model.MoudelTitle),
                    DbHelper.CreateInSqlParameter("@In_IsPage",DbType.Boolean,model.IsPage),
                    DbHelper.CreateInSqlParameter("@In_remark",DbType.AnsiString,model.remark),
                    //DbHelper.CreateInSqlParameter("@In_FunctionName",DbType.AnsiString,model.FunctionName),
                    DbHelper.CreateInSqlParameter("@In_isdel",DbType.Int32,model.isdel)
            };
            return DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), CmdParams);
        }
        /// <summary>
        /// 新增一个按钮实体
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddCon(Model.Sys_NavigationInfo model)
        {
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("INSERT INTO Sys_Navigation(");
            strSql.Append("Id,NaviName,picsrc,ParentID,Sort,IsPage,ControlID,remark,functionName,isdel)");
            strSql.Append("VALUES(");
            strSql.Append("@In_Id,@In_NaviName,@In_picsrc,@In_ParentID,@In_Sort,@In_IsPage,@In_ControlID,@In_remark,@In_FunctionName,@In_isdel)");
            System.Data.SqlClient.SqlParameter[] CmdParams = new System.Data.SqlClient.SqlParameter[] {
                    DbHelper.CreateInSqlParameter("@In_Id",DbType.AnsiString,Guid.NewGuid().ToString()),
                    DbHelper.CreateInSqlParameter("@In_NaviName",DbType.AnsiString,model.NaviName),
                    DbHelper.CreateInSqlParameter("@In_picsrc",DbType.AnsiString,model.picsrc),
                    DbHelper.CreateInSqlParameter("@In_ParentID",DbType.String,model.ParentID),
                    DbHelper.CreateInSqlParameter("@In_Sort",DbType.Int32,model.Sort),
                    DbHelper.CreateInSqlParameter("@In_IsPage",DbType.Boolean,model.IsPage),
                    DbHelper.CreateInSqlParameter("@In_ControlID",DbType.AnsiString,model.ControlID),
                    //DbHelper.CreateInSqlParameter("@In_IsFunction",DbType.Boolean,model.IsFunction),
                    DbHelper.CreateInSqlParameter("@In_remark",DbType.AnsiString,model.remark),
                    DbHelper.CreateInSqlParameter("@In_FunctionName",DbType.AnsiString,model.FunctionName),
                    DbHelper.CreateInSqlParameter("@In_isdel",DbType.Int32,model.isdel)
            };
            return DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), CmdParams);
        }
        /// <summary>
        /// 根据ID修改一个导航节点实体
        /// </summary>
        public int ModifyNav(Model.Sys_NavigationInfo model)
        {
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("UPDATE Sys_Navigation SET ");
            strSql.Append("NaviName = @In_NaviName,");
            strSql.Append("pagesrc = @In_pagesrc,");
            strSql.Append("picsrc = @In_picsrc,");
            strSql.Append("ParentID = @In_ParentID,");
            strSql.Append("SysType = @In_SysType,");
            strSql.Append("ClassName = @In_ClassName,");
            strSql.Append("MoudelTitle = @In_MoudelTitle,");
            strSql.Append("IsPage = @In_IsPage,");
            strSql.Append("remark = @In_remark,");
            strSql.Append("isdel = @In_isdel ");
            strSql.Append(" WHERE ID=@In_ID ");
            System.Data.SqlClient.SqlParameter[] CmdParams = new System.Data.SqlClient.SqlParameter[] {
                    DbHelper.CreateInSqlParameter("@In_NaviName",DbType.AnsiString,model.NaviName),
                    DbHelper.CreateInSqlParameter("@In_pagesrc",DbType.AnsiString,model.pagesrc),
                    DbHelper.CreateInSqlParameter("@In_picsrc",DbType.AnsiString,model.picsrc),
                    DbHelper.CreateInSqlParameter("@In_ParentID",DbType.String,model.ParentID),
                    DbHelper.CreateInSqlParameter("@In_SysType",DbType.String,model.SysType),
                    DbHelper.CreateInSqlParameter("@In_ClassName",DbType.AnsiString,model.ClassName),
                    DbHelper.CreateInSqlParameter("@In_MoudelTitle",DbType.AnsiString,model.MoudelTitle),
                    DbHelper.CreateInSqlParameter("@In_IsPage",DbType.Boolean,model.IsPage),
                    DbHelper.CreateInSqlParameter("@In_remark",DbType.AnsiString,model.remark),
                    DbHelper.CreateInSqlParameter("@In_isdel",DbType.Int32,model.isdel),
                    DbHelper.CreateInSqlParameter("@In_Id",DbType.String,model.Id)};
            return DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), CmdParams);
        }

        /// <summary>
        /// 根据ID修改一个按钮实体
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int ModifyCon(Model.Sys_NavigationInfo model)
        {
            System.Text.StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE Sys_Navigation SET ");
            strSql.Append("NaviName = @In_NaviName,");
            strSql.Append("picsrc = @In_picsrc,");
            strSql.Append("ParentID = @In_ParentID,");
            strSql.Append("IsPage = @In_IsPage,");
            strSql.Append("ControlID = @In_ControlID,");
            //strSql.Append("IsFunction = @In_IsFunction,");
            strSql.Append("remark = @In_remark,");
            strSql.Append("FunctionName = @In_FunctionName,");
            strSql.Append("isdel = @In_isdel ");
            strSql.Append(" WHERE ID=@In_ID ");
            System.Data.SqlClient.SqlParameter[] CmdParams = new System.Data.SqlClient.SqlParameter[] {
                    DbHelper.CreateInSqlParameter("@In_NaviName",DbType.AnsiString,model.NaviName),
                    DbHelper.CreateInSqlParameter("@In_picsrc",DbType.AnsiString,model.picsrc),
                    DbHelper.CreateInSqlParameter("@In_ParentID",DbType.String,model.ParentID),
                    DbHelper.CreateInSqlParameter("@In_IsPage",DbType.Boolean,model.IsPage),
                    DbHelper.CreateInSqlParameter("@In_ControlID",DbType.AnsiString,model.ControlID),
                    //DbHelper.CreateInSqlParameter("@In_IsFunction",DbType.Boolean,model.IsFunction),
                    DbHelper.CreateInSqlParameter("@In_remark",DbType.AnsiString,model.remark),
                    DbHelper.CreateInSqlParameter("@In_FunctionName",DbType.AnsiString,model.FunctionName),
                    DbHelper.CreateInSqlParameter("@In_isdel",DbType.Int32,model.isdel),
                    DbHelper.CreateInSqlParameter("@In_Id",DbType.String,model.Id)};
            return DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), CmdParams);
        }
        /// <summary>
        /// 根据ID删除一个实体
        /// </summary>
        public int Del(string id)
        {
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("UPDATE Sys_Navigation SET isdel=1 ");
            strSql.Append("WHERE Id in (" + id + ")");
            return DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), null);
        }

       
        /// <summary>
        /// 获取列表数据
        /// </summary>
        public List<Model.Sys_NavigationInfo> GetList()
        {
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("select * from Sys_Navigation WHERE isdel=0 ORDER BY sort,id");
            System.Data.SqlClient.SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null);
            return GetList(dr);
        }

        /// <summary>
        /// 根据ID获取一个实体
        /// </summary>
        public Model.Sys_NavigationInfo GetModel(string id)
        {
            Model.Sys_NavigationInfo model = new Model.Sys_NavigationInfo();
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("select * from Sys_Navigation WHERE id='" + id + "'");
            System.Data.SqlClient.SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null);
            for (; dr.Read(); )
            {
                model = GetModel(dr);
            }
            dr.Close();
            return model;
        }

        /// <summary>
        /// 获取一个实体
        /// </summary>
        private Model.Sys_NavigationInfo GetModel(System.Data.SqlClient.SqlDataReader dr)
        {
            Model.Sys_NavigationInfo model = new Model.Sys_NavigationInfo();
            model.Id = dr["Id"].ToString();
            model.NaviName = dr["NaviName"].ToString();
            model.pagesrc = dr["pagesrc"].ToString();
            model.picsrc = dr["picsrc"].ToString();
            model.ParentID = dr["ParentID"].ToString();
            if (dr["SysType"].ToString() != "")
            {
                model.SysType = dr["SysType"].ToString();
            }
            
            if (dr["Sort"].ToString() != "")
            {
                model.Sort = int.Parse(dr["Sort"].ToString());
            }
            model.ClassName = dr["ClassName"].ToString();
            model.MoudelTitle = dr["MoudelTitle"].ToString();

            if (dr["IsPage"].ToString() != "")
            {
                model.IsPage = bool.Parse(dr["IsPage"].ToString());
            }
            model.ControlID = dr["ControlID"].ToString();
            //if (dr["IsFunction"].ToString() != "")
            //{
            //    model.IsFunction = bool.Parse(dr["IsFunction"].ToString());
            //}
            model.remark = dr["remark"].ToString();
            model.FunctionName = dr["FunctionName"].ToString();
            if (dr["isdel"].ToString() != "")
            {
                model.isdel = int.Parse(dr["isdel"].ToString());
            }
            return model;
        }

        /// <summary>
        /// 获取一个列表
        /// </summary>
        public List<Model.Sys_NavigationInfo> GetList(System.Data.SqlClient.SqlDataReader dr)
        {
            List<Model.Sys_NavigationInfo> Lst = new List<Model.Sys_NavigationInfo>();
            for (; dr.Read(); )
            {
                Lst.Add(GetModel(dr));
            }
            dr.Close();
            return Lst;
       }
        /// <summary>
        /// 获取列表数据(带条件的)
        /// </summary>
        public List<Model.Sys_NavigationInfo> GetconList(string condition)
        {
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("select * from Sys_Navigation WHERE ");
            System.Data.SqlClient.SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString() + condition, null);
            return GetList(dr);
        }

     /// <summary>
        /// 根据条件，得到数据的条数
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns>返回条数</returns>
        public int GetCount(string tablename,string condition)
        {
            return DbHelper.GetCount(tablename, condition);
        }

        public SqlDataReader GetPageList(string field, string condition, int pageSize, int pageIndex, string tbNames, string sortNames, bool sortType)
        {
            return DbHelper.GetPageList( field,condition,pageSize,pageIndex,tbNames,sortNames,sortType);
 
        }


        /////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>
        /// 获取导航的父节点
        /// </summary>
        /// <returns>所有的父节点</returns>
        public List<Model.Sys_NavigationInfo> GetParentList()
        {
            System.Text.StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from Sys_Navigation where IsPage = 1 and isdel=0 order by NaviName");
            System.Data.SqlClient.SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null);
            return GetList(dr);
        }
        /// <summary>
        /// 根据ID获取导航的所属子节点
        /// </summary>
        /// <returns></returns>
        public List<Model.Sys_NavigationInfo> GetChildList(string id)
        {
            System.Text.StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from Sys_Navigation where ParentID = '" + id + "'  and isdel = 0 order by sort,id ");
            SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null);
            return GetList(dr);
        }


        /// <summary>
        /// 根据按钮ID和当前对应页面 查找是否 存在该按钮ID
        /// </summary>
        /// <param name="ConId">按钮ID</param>
        /// <param name="ConId">当前对应页面ID</param>
        /// <returns></returns>
        public string ExistConID(string ConId, string ParentID)
        {
            System.Text.StringBuilder strSql = new StringBuilder();
            strSql.Append(" ControlID = '" + ConId + "' and IsPage = 0 and isdel=0 and ParentID ='" + ParentID + "'");
            int exist = 0;
            exist = DbHelper.GetCount("Sys_Navigation", strSql.ToString());
            if (exist >= 1) return "1";
            else return "0";
        }


        /// <summary>
        /// 根据用户ID获取符合权限的按钮
        /// </summary>
        /// <returns></returns>
        public List<Model.Sys_NavigationInfo> GetUserControl(string id, string ClassName)
        {
            System.Text.StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT DISTINCT a.* FROM Sys_Navigation a join Sys_RoleNavigation b");
            strSql.Append(" on a.id=b.NavigationId join sys_UserRole c on c.roleid =b.RoleID");
            strSql.Append(" join Sys_Navigation d on a.ParentID=d.Id");
            strSql.Append(" where a.isdel =0 and a.IsPage =0 and");
            strSql.Append(" d.ClassName like '%" + ClassName + "%' and c.UserID ='" + id + "'  order by a.sort,a.id");
            SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null);
            return GetList(dr);
        }

        /// <summary>
        /// 根据用户ID获取所有系统菜单(包括页面和按钮)
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Model.Sys_NavigationInfo> GetNavByUserId(string id)
        {

            System.Text.StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.Id,a.NaviName,a.pagesrc,a.picsrc,a.ParentID,a.SysType,");
            strSql.Append(" a.Sort,a.ClassName,a.MoudelTitle,a.IsPage,a.FunctionName,a.ControlID,");
            strSql.Append(" a.isdel,a.remark  from Sys_Navigation a");
            strSql.Append(" join Sys_RoleNavigation b on a.Id = b.NavigationID ");
            strSql.Append("join sys_UserRole c on b.RoleID = c.roleid ");
            strSql.Append("where UserID = '" + id + "' and isdel = 0  ");
            strSql.Append(" GROUP BY  a.Id,a.NaviName,a.pagesrc,a.picsrc,a.ParentID,");
            strSql.Append(" a.Sort,a.ClassName,a.MoudelTitle,a.IsPage,a.SysType,a.FunctionName,a.ControlID,");
            strSql.Append(" a.isdel,a.remark  order by a.sort,a.id ");
            SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null);
            return GetList(dr);
        }

        /// <summary>
        /// 根据用户ID获取所有系统菜单
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Model.Sys_NavigationInfo> GetAllNav(string id)
        {
           
            System.Text.StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.Id,a.NaviName,a.pagesrc,a.picsrc,a.ParentID,a.SysType,");
            strSql.Append(" a.Sort,a.ClassName,a.MoudelTitle,a.IsPage,a.FunctionName,a.ControlID,");
            strSql.Append(" a.isdel,a.remark  from Sys_Navigation a");
            strSql.Append(" join Sys_RoleNavigation b on a.Id = b.NavigationID ");
            strSql.Append("join sys_UserRole c on b.RoleID = c.roleid ");
            strSql.Append("where UserID = '" + id + "' and isdel = 0 and IsPage = 1 ");
            strSql.Append(" GROUP BY  a.Id,a.NaviName,a.pagesrc,a.picsrc,a.ParentID,");
            strSql.Append(" a.Sort,a.ClassName,a.MoudelTitle,a.IsPage,a.SysType,a.FunctionName,a.ControlID,");
            strSql.Append(" a.isdel,a.remark  order by a.sort,a.id ");
            SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null);
            return GetList(dr);
        }

        /// <summary>
        /// 根据用户ID获取业务系统菜单
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Model.Sys_NavigationInfo> GetYewuNav(string id)
        {

            System.Text.StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.Id,a.NaviName,a.pagesrc,a.picsrc,a.ParentID,a.SysType,");
            strSql.Append(" a.Sort,a.ClassName,a.MoudelTitle,a.IsPage,a.FunctionName,a.ControlID,");
            strSql.Append(" a.isdel,a.remark  from Sys_Navigation a");
            strSql.Append(" join Sys_RoleNavigation b on a.Id = b.NavigationID ");
            strSql.Append("join sys_UserRole c on b.RoleID = c.roleid ");
            strSql.Append("where UserID = '" + id + "' and SysType='业务系统' and isdel = 0 and IsPage = 1 ");
            strSql.Append(" GROUP BY  a.Id,a.NaviName,a.pagesrc,a.picsrc,a.ParentID,");
            strSql.Append(" a.Sort,a.ClassName,a.MoudelTitle,a.IsPage,a.SysType,a.FunctionName,a.ControlID,");
            strSql.Append(" a.isdel,a.remark  order by a.sort,a.id ");
            SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null);
            return GetList(dr);
        }

        /// <summary>
        /// 根据ID获取所属子节点的最大排序号
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int GetChildMaxSort(string id)
        {
            System.Text.StringBuilder strSqL = new StringBuilder();
            strSqL.Append("select max(sort) from Sys_Navigation where ParentID = '" + id + "' and isdel = 0 ");
            return Common.MyConvert.toInt32(DbHelper.ExecuteScalar(CommandType.Text, strSqL.ToString(), null));
        }

        public void SetChildSort(string id, int sort)
        {
            System.Text.StringBuilder strSql = new StringBuilder();
            strSql.Append("update Sys_Navigation set sort = '" + sort + "' where id ='" + id + "'");
            DbHelper.ExecuteNonQuery(strSql.ToString());
        }

        /// <summary>
        /// 根据当前的页面名（如S_compList）获取其中文名（如单位管理）
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public string GetModuleName(string name)
        {
            System.Text.StringBuilder strSql = new StringBuilder();
            strSql.Append("select NaviName from Sys_Navigation where ClassName like '%" + name + "%' and isdel = 0");
            return Common.MyConvert.toString(DbHelper.ExecuteScalar(strSql.ToString()));

        }

        /// <summary>
        /// 获取后台管理的模块名
        /// </summary>
        /// <returns></returns>
        public List<Model.Sys_NavigationInfo> GetModule()
        {
            System.Text.StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from Sys_Navigation where IsPage = 1 and isdel = 0 and ParentID='35ADC11E-D94D-4A72-90A0-52BC01FAF3AC' order by sort");
            SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null);
            return GetList(dr);
        }
    }
}



